<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars 
define("PRIMARY_KEY","kurssi_id");
$KEY[]         = array("PRIMARY_KEY"=>PRIMARY_KEY);
$FileName      = "ais_transaction_invjournal.php";
$FileTemplate  = "ais_transaction_invjournal_list.html";
$TableName     = "ais_memorial";
$SubHeaderTitle= "Transaction Invalid Journal";
$PageSize      = 10 ;
$HTMLGridList  = "CurrencyGrid";
//************ FORM ACTION **************
//$TEMPORARY = "TEMPORARY";
//Initiate Database Table For Search;
$FYear = GetParam("periodYear","");
$FMonth = GetParam("periodMonth","");

$year = substr($FYear,-2);
if ($FMonth<10) $month = "0".$FMonth;
else $month = $FMonth;


//******* DATABASE FORM QUERY ***********
$SQL =     "DROP IF EXISTS TABLE z_temp_a";
$DBConnection->QueryNoResult($SQL);

$SQL =     "DROP IF EXISTS TABLE z_temp_b";
$DBConnection->QueryNoResult($SQL);

$SQL =     "DROP IF EXISTS TABLE ais_journalerror";
$DBConnection->QueryNoResult($SQL);


$SQL =     "
			CREATE $TEMPORARY TABLE z_temp_a (
             nobkt VARCHAR(255) NOT NULL,
             total DECIMAL(15,5) NOT NULL
            )TYPE=MyISAM

            ";
$DBConnection->QueryNoResult($SQL);

$SQL =     "
			CREATE $TEMPORARY TABLE z_temp_b (
             nobkt VARCHAR(255) NOT NULL,
             total DECIMAL(15,5) NOT NULL
            )TYPE=MyISAM
		   ";
$DBConnection->QueryNoResult($SQL);

$SQL =     "
			CREATE $TEMPORARY TABLE ais_journalerror (
			 ledger VARCHAR(4) NOT NULL,
             ktrasl VARCHAR(3) NOT NULL,
             nobkt VARCHAR(32) NOT NULL,
             noref VARCHAR(32) NOT NULL,
             tglbkt DATE,
             errordate VARCHAR(64) NOT NULL,
             errorcoa VARCHAR(64) NOT NULL,
             errordata1 VARCHAR(64) NOT NULL,
             errordata2 VARCHAR(64) NOT NULL,
             errordata3 VARCHAR(64) NOT NULL,
             errordata4 VARCHAR(64) NOT NULL,
             errordata5 VARCHAR(64) NOT NULL,
             errordata6 VARCHAR(64) NOT NULL,
             errordata7 VARCHAR(64) NOT NULL,
             errordata8 VARCHAR(64) NOT NULL,
             errordata9 VARCHAR(64) NOT NULL
            )TYPE=MyISAM

            ";
$DBConnection->QueryNoResult($SQL);

$SQL =    "
		   INSERT INTO z_temp_a (nobkt,total)
           SELECT ais_memorial.nobkt,SUM(ais_memorial.jumlah)
           FROM ais_memorial WHERE ais_memorial.urut <> '999'
           GROUP BY ais_memorial.nobkt
           
		  ";
$DBConnection->QueryNoResult($SQL);

$SQL =    "
		  INSERT INTO z_temp_b (nobkt,total)
          SELECT ais_memorial.nobkt,ais_memorial.jumlah
          FROM ais_memorial WHERE ais_memorial.urut = '999'
		  ";
$DBConnection->QueryNoResult($SQL);


$SQL =   "

SELECT

ais_memorial.ledger,ais_memorial.ktrasl,

ais_memorial.nobkt,ais_memorial.noref,

ais_memorial.tglbkt,

IF(SUBSTRING(ais_memorial.nobkt,3,4) <> DATE_FORMAT(ais_memorial.tglbkt,'%y%m'),' - Invalid Date','') as errordate,

IF(IFNULL(tblcoa.skada,'')='',' - Invalid COA','') as errorcoa,

IF(substring(ais_memorial.nobkt,1,2)='TK',
IF(substring(ais_memorial.noref,1,2)='ND',
IF(ais_piutang.nobkt='','',' - Not Enough Data On AR'),''),'') as errordata1,

IF(substring(ais_memorial.nobkt,1,2)='TB',
IF(substring(ais_memorial.noref,1,2)='ND',
IF(ais_piutang.nobkt='','',' - Not Enough Data On AR'),''),'') as errordata2,

IF(substring(ais_memorial.nobkt,1,2)='BK',
IF(substring(ais_memorial.noref,1,2)='MM',
IF(ais_hutang.nobkt='','',' - Not Enough Data'),''),'') as errordata3,

IF(substring(ais_memorial.nobkt,1,2)='BB',
IF(substring(ais_memorial.noref,1,2)='MM',
IF(ais_hutang.nobkt='','',' - Not Enough Data'),''),'') as errordata4,

IF(substring(ais_memorial.nobkt,1,2)='TK',
IF(substring(ais_memorial.noref,1,2)='KW',
IF(ais_receipt.nokwit='','',' - Not Enough Data'),''),'') as errordata5,

IF(substring(ais_memorial.nobkt,1,2)='TB',
IF(substring(ais_memorial.noref,1,2)='KW',
IF(ais_receipt.nokwit='','',' - Not Enough Data'),''),'') as errordata6,

IF(substring(ais_memorial.nobkt,1,2)='BK',
IF(substring(ais_memorial.noref,1,2)='BS',
IF(ais_advance.nobkt='','',' - Not Enough Data'),''),'') as errordata7,

IF(substring(ais_memorial.nobkt,1,2)='BB',
IF(substring(ais_memorial.noref,1,2)='BS',
IF(ais_advance.nobkt='','',' - Not Enough Data'),''),'') as errordata8,

IF(z_temp_a.total=z_temp_b.total,'',' - Saldo Not Balance') as errordata9

FROM ais_memorial

LEFT JOIN tblcoa ON tblcoa.skada=ais_memorial.skada
LEFT JOIN ais_piutang ON ais_piutang.nobkt=ais_memorial.nobkt AND ais_piutang.normut=ais_memorial.urut
LEFT JOIN ais_hutang ON ais_hutang.nobkt=ais_memorial.nobkt AND ais_hutang.normut=ais_memorial.urut
LEFT JOIN ais_receipt ON ais_receipt.nokwit=ais_memorial.nobkt 
LEFT JOIN ais_advance ON ais_advance.nobkt=ais_memorial.nobkt
LEFT JOIN z_temp_a ON z_temp_a.nobkt=ais_memorial.nobkt
LEFT JOIN z_temp_b ON z_temp_b.nobkt=ais_memorial.nobkt

WHERE

(IFNULL(IF(SUBSTRING(ais_memorial.nobkt,3,3) <> '$subnobkt',' - Invalid Date',''),'') <> ''

OR

IFNULL(IF(tblcoa.skada='',' - Invalid COA',''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TK',
IF(substring(ais_memorial.noref,1,2)='ND',
IF(ais_piutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TB',
IF(substring(ais_memorial.noref,1,2)='ND',
IF(ais_piutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BK',
IF(substring(ais_memorial.noref,1,2)='MM',
IF(ais_hutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BB',
IF(substring(ais_memorial.noref,1,2)='MM',
IF(ais_hutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TK',
IF(substring(ais_memorial.noref,1,2)='KW',
IF(ais_receipt.nokwit='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TB',
IF(substring(ais_memorial.noref,1,2)='KW',
IF(ais_receipt.nokwit='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BK',
IF(substring(ais_memorial.noref,1,2)='BS',
IF(ais_advance.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BB',
IF(substring(ais_memorial.noref,1,2)='BS',
IF(ais_advance.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(z_temp_a.total=z_temp_b.total,'',' - Saldo Not Balance'),'') <> '')

AND ais_memorial.ledger = '$year$month' AND ais_memorial.ktrasl = '".GetOfficeID()."'

";

$SQLCount =   "

SELECT
COUNT(*)

FROM ais_memorial

LEFT JOIN tblcoa ON tblcoa.skada=ais_memorial.skada
LEFT JOIN ais_piutang ON ais_piutang.nobkt=ais_memorial.nobkt
LEFT JOIN ais_hutang ON ais_hutang.nobkt=ais_memorial.nobkt
LEFT JOIN ais_receipt ON ais_receipt.nokwit=ais_memorial.nobkt
LEFT JOIN ais_advance ON ais_advance.nobkt=ais_memorial.nobkt
LEFT JOIN z_temp_a ON z_temp_a.nobkt=ais_memorial.nobkt
LEFT JOIN z_temp_b ON z_temp_b.nobkt=ais_memorial.nobkt

WHERE

(IFNULL(IF(SUBSTRING(ais_memorial.nobkt,3,3) <> '$subnobkt',' - Invalid Date',''),'') <> ''

OR

IFNULL(IF(tblcoa.skada='',' - Invalid COA',''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TK',
IF(substring(ais_memorial.noref,1,2)='ND',
IF(ais_piutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TB',
IF(substring(ais_memorial.noref,1,2)='ND',
IF(ais_piutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BK',
IF(substring(ais_memorial.noref,1,2)='MM',
IF(ais_hutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BB',
IF(substring(ais_memorial.noref,1,2)='MM',
IF(ais_hutang.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TK',
IF(substring(ais_memorial.noref,1,2)='KW',
IF(ais_receipt.nokwit='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='TB',
IF(substring(ais_memorial.noref,1,2)='KW',
IF(ais_receipt.nokwit='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BK',
IF(substring(ais_memorial.noref,1,2)='BS',
IF(ais_advance.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(substring(ais_memorial.nobkt,1,2)='BB',
IF(substring(ais_memorial.noref,1,2)='BS',
IF(ais_advance.nobkt='','',' - Not Enough Data'),''),''),'') <> ''

OR

IFNULL(IF(z_temp_a.total=z_temp_b.total,'',' - Saldo Not Balance'),'') <> '')

AND ais_memorial.ledger = '$year$month' AND ais_memorial.ktrasl = '".GetOfficeID()."' AND ais_memorial.urut=999

GROUP BY memorial_id
";


//******* SHOW FORM CONTENT *********
$PageNum = 1;
$RecCnt = $DBConnection->GetResSQL($SQLCount) ; 


if (GetParam("PageNum","")) $PageNum  = GetParam("PageNum","");

$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);
$TBS->MergeBlock("monthformblk",GetArrayMonth());
$TBS->MergeBlock("yearformblk",GetArrayYear());
$TBS->MergeBlock("blg",$DBMysql,$SQL,$PageSize,$PageNum,$RecCnt);
$TBS->MergeNavigationBar('nv',array('size'=>10,'pos'=>'centred'),$PageNum,$RecCnt,$PageSize) ; 
$TBS->Show();


function m_event_b1($BlockName,&$CurrRec,&$DetailSrc,$RecNum){
//$BlockName : name of the block that calls the function (read only)
//$CurrRec   : array that contains columns of the current record (read/write)
//$DetailSrc : source of the current section (read/write)
//$RecNum    : number of the current record (read only)
  $CurrRec['errordata'] = $CurrRec['errordate'];
  if (strlen($CurrRec['errordata'])) {
  	if (strlen($CurrRec['errorcoa']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errorcoa'];
  } else $CurrRec['errordata'] = $CurrRec['errorcoa'];
  
  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata1']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata1'];
  } else $CurrRec['errordata'] = $CurrRec['errordata1'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata2']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata2'];
  } else $CurrRec['errordata'] = $CurrRec['errordata2'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata3']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata3'];
  } else $CurrRec['errordata'] = $CurrRec['errordata3'];
  
  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata4']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata4'];
  } else $CurrRec['errordata'] = $CurrRec['errordata4'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata5']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata5'];
  } else $CurrRec['errordata'] = $CurrRec['errordata5'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata6']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata6'];
  } else $CurrRec['errordata'] = $CurrRec['errordata6'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata7']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata7'];
  } else $CurrRec['errordata'] = $CurrRec['errordata7'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata8']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata8'];
  } else $CurrRec['errordata'] = $CurrRec['errordata8'];

  if (strlen($CurrRec['errordata'])) {
	if (strlen($CurrRec['errordata9']))
  	$CurrRec['errordata'] .= ','.$CurrRec['errordata9'];
  } else $CurrRec['errordata'] = $CurrRec['errordata9'];


  
} 
?>